if exists (select 1 from sysobjects where name = 'set_zusatzpersonplan' and type = 'P')
begin
   drop procedure set_zusatzpersonplan
   print 'Procedure: set_zusatzpersonplan deleted ...'
end
go
create procedure set_zusatzpersonplan(
  @planid            int           = 18,
  @prsid            int            = 26,
  @abteilungid       int           = 1
)
as
begin
  set nocount on

  --insert into arbeitsplanzprs(planid, zprsid, abteilungid) values (@planid, @zprsid, @abteilungid)
  --insert into Arbeitszeit(prsid,planid,abteilungid,soll,haben,ferien) values (@zprsid,@planid,@abteilungid,0,0,0)

  declare @maxprsschemaid    int

  select @maxprsschemaid = max(ps.PrsSchemaID)
    from ArbeitsPlan ap
   inner join PlanSchema ps
      on ps.SchemaID = ap.SchemaID
   where ap.PlanID = @planid
     and ps.AbteilungID = @abteilungid

  update PlanSchema
     set PrsSchemaID = ps.PrsSchemaID + 1
    from PlanSchema ps
   inner join ArbeitsPlan ap
      on ps.SchemaID = ap.SchemaID
   where ap.PlanID = @planid
     and ps.PrsSchemaID > @maxprsschemaid

  insert PlanSchema
         (SchemaID,
          PrsSchemaID,
          PrsID,
          AbteilungID,
          RGB,
          ColorName,
          Bezeichner)
   select ap.SchemaID,
          @maxprsschemaid + 1,
          @prsid,
          @abteilungid,
          -1,
          '',''
      from ArbeitsPlan ap
   inner join PlanSchema ps
      on ps.SchemaID = ap.SchemaID
   where ap.PlanID = @planid
   group by ap.SchemaID

end
go
print 'Procedure: set_zusatzpersonplan done ...'
go
grant exec on set_zusatzpersonplan to prsadmins with grant option
go
grant exec on set_zusatzpersonplan to prsusers
go

